/*
* CourseRegisterDaoImpl.java
* Created on  2014-5-7 下午5:27
* 版本       修改时间          作者      修改内容
* V1.0.1    2014-5-7       gaoxinyu    初始版本
*
*/
package com.genghis.ptas.course.dao.impl;

import com.genghis.core.page.dao.PageDao;
import com.genghis.core.page.entity.Page;
import com.genghis.core.page.entity.PageParameter;
import com.genghis.ptas.course.dao.CourseRegisterDao;
import com.genghis.ptas.course.entity.ChapterInfo;
import com.genghis.ptas.course.entity.ChapterRegister;
import com.genghis.ptas.course.entity.CourseRegister;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 类的描述信息
 *
 * @author gaoxinyu
 * @version 1.0.1
 */
@Repository("courseRegisterDao")
public class CourseRegisterDaoImpl extends PageDao implements CourseRegisterDao {

    private StringBuffer getCourseRegisterSql() {
        String sql = "SELECT a.* , course_info.course_name, course_info.up_user_name FROM\n" +
                "  (SELECT cu.*, su.`user_name` AS student_name FROM course_register cu LEFT JOIN sys_user su ON cu.`student_id` = su.`id`) a \n" +
                "  LEFT JOIN \n" +
                "  (SELECT  c.`id`, c.`course_name`, s.`user_name` AS up_user_name FROM course c LEFT JOIN sys_user s ON c.`up_user_id` = s.`id` ) course_info\n" +
                "  ON a.`course_id` = course_info.id \n" +
                "WHERE 1 = 1 ";
        return new StringBuffer(sql);
    }

    @Override
    public Page<CourseRegister> findCourseRegisterPageList(PageParameter pageParameter, CourseRegister courseRegister) {
        StringBuffer sql = getCourseRegisterSql();
        if (!"".equals(courseRegister.getStudentName()) && courseRegister.getStudentName() != null) {
            sql.append(" AND student_name like '%").append(courseRegister.getStudentName()).append("%'");
        }
        if (!"".equals(courseRegister.getCourseName()) && courseRegister.getCourseName() != null) {
            sql.append(" AND course_name like '%").append(courseRegister.getCourseName()).append("%'");
        }
        return queryForPage(pageParameter, sql.toString(), new BeanPropertySqlParameterSource(courseRegister), new BeanPropertyRowMapper<CourseRegister>(CourseRegister.class));
    }

    @Override
    public CourseRegister findCourseRegisterById(Integer id) {
        String sql = "select * from course_register where id = ?";
        return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<CourseRegister>(CourseRegister.class), id);
    }

    @Override
    public List<ChapterRegister> findCourseChapterRegisterByCourseIdAndUserId(Integer id, Integer userId) {
        String sql = "SELECT chr.*,cc.chapter_name,cc.chapter_url,cc.xml_chapter_id,cc.parent_id \n" +
                "FROM course_chapter_register chr  \n" +
                "LEFT JOIN `course_chapter` cc ON chr.`course_chapter_id` = cc.`id` \n" +
                "LEFT JOIN `course_register` cr ON chr.`course_register_id`=cr.`id` WHERE chr.student_id = ? AND cr.course_id = " + id;
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<ChapterRegister>(ChapterRegister.class), userId);
    }

    @Override
    public ChapterRegister findCourseChapterRegisterById(Integer id) {
        String sql = "select * from course_chapter_register where id = " + id;
        return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<ChapterRegister>(ChapterRegister.class));
    }

    @Override
    public void editCourseRegister(CourseRegister courseRegister) {
        String sql = "UPDATE course_register SET course_id = :courseId, score = :score, note_id = :noteId, state = :state," +
                " student_id = :studentId, last_time = :lastTime, total_time = :totalTime WHERE id = :id ";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(courseRegister));
    }

    @Override
    public void editCourseChapterRegister(ChapterRegister chapterRegister) {
        String sql = "UPDATE course_chapter_register SET lesson_status = :lessonStatus, session_time = :sessionTime, " +
                " suspend_data = :suspendData, entry = :entry, total_time = :totalTime, lesson_mode = :lessonMode, " +
                "core_exit = :coreExit, lesson_location = :lessonLocation, score_raw = :scoreRaw WHERE id = :id ";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(chapterRegister));
    }

    @Override
    public void deleteCourseRegisterById(Integer id) {
        String sql = "DELETE FROM course_register WHERE id = ?";
        getJdbcTemplate().update(sql, id);
    }


    @Override
    public int addCourseRegister(CourseRegister courseRegister) {
        String sql = "INSERT INTO course_register(course_id, state, student_id, note_id,total_time, score, register_date) " +
                "VALUES(:courseId, :state , :studentId, :noteId, :totalTime, :score, :registerDate)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(courseRegister), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public int addCourseChapterRegister(ChapterRegister chapterRegister) {
        String sql = "INSERT INTO course_chapter_register(course_chapter_id, course_register_id , lesson_status, student_id) " +
                "VALUES(:courseChapterId, :courseRegisterId, :lessonStatus, :studentId)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(chapterRegister), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public List<CourseRegister> findCourseRegisterByCourseIdAndUserId(Integer courseId, Integer userId) {
        String sql = "select * from course_register where course_id = ? AND student_id = " + userId;
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<CourseRegister>(CourseRegister.class), courseId);
    }

    @Override
    public List<CourseRegister> findCourseRegisterByUserId(Integer userId) {
        String sql = "SELECT c.`course_type`,c.`course_name`,c.`photo_url`,cr.* FROM course_register cr " +
                "LEFT JOIN course c ON cr.course_id=c.id where student_id = ?";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<CourseRegister>(CourseRegister.class), userId);
    }

    @Override
    public List<ChapterRegister> findCourseChapterRegisterByCourseRegisterId(Integer id) {
        String sql = "SELECT ccr.* FROM `course_chapter_register` ccr LEFT JOIN " +
                "`course_register` cr ON ccr.`course_register_id` = cr.`id` WHERE cr.`id` = ?";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<ChapterRegister>(ChapterRegister.class), id);
    }
}
